﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using AssetManagementSystem.Utils;

namespace AssetManagementSystem.Model.Dao.MSAccess {
    public class HoldDAO  : AssetManagementSystem.Model.IDao.IHoldDAO {

        //The MSAccess database connection to use in all data access operations.
        private OleDbConnection dbConn;

        /// <summary>
        /// Constructs a new HoldDAO using a MSAccess database connection string.
        /// </summary>
        /// <param name="connectionString">MSAccess database connection string</param>
        public HoldDAO(string connectionString) {
            //create new database connection
            this.dbConn = new OleDbConnection(connectionString);
            //test the new database connection
            try {
                //make sure the connection can be opened.
                dbConn.Open();
            } catch (Exception e) {
                throw e;
            } finally {
                //close connection if goes went wrong.
                dbConn.Close();
            }
        }

        //====================================================================
        //  CRUD METHODS - CREATE, READ, UPDATE, DELETE
        //====================================================================

        /// <summary>
        /// Create a new hold in the database, from an 'Hold' business object.
        /// </summary>
        /// <param name="hold">The hold object to be stored in the database</param>
        public void CreateHold(Hold hold) {
           try {
                //store the new hold
                dbConn.Open();
                OleDbCommand oleDbCmd = new OleDbCommand(
                    "INSERT INTO Hold ([StudentId], [AssetId], [HoldDate]) " +
                    "VALUES(@StudentId, @AssetId, @HoldDate)",
                    dbConn);
                oleDbCmd.Parameters.AddWithValue("@StudentId", hold.StudentId);
                oleDbCmd.Parameters.AddWithValue("@AssetId", hold.AssetId);
                OleDbParameter p2 = oleDbCmd.Parameters.AddWithValue("@HoldDate", hold.DateCreated);
                p2.OleDbType = OleDbType.Date;
                //make sure the database changed
                if (oleDbCmd.ExecuteNonQuery() < 1) {
                    throw new Exception("Hold not stored. An unknown error occured");
                }
            } catch (Exception e) {
                throw e;
            } finally {
                dbConn.Close();
            }
        }

        /// <summary>
        /// Returns a collection of all holds found within the database.
        /// </summary>
        /// <returns>A collection of all holds found within the database.</returns>
        public ICollection<Hold> GetAllHolds() {
            ICollection<Hold> allHolds = new List<Hold>();
            try {
                dbConn.Open();
                OleDbDataAdapter dAdapter = new OleDbDataAdapter("SELECT * FROM [Hold]", dbConn);
                DataTable dataTable = new DataTable();
                dAdapter.Fill(dataTable);
                //create an Loan object for each record returned
                foreach (DataRow holdRow in dataTable.Rows) {
                    allHolds.Add(new Hold(
                        holdRow["StudentId"].ToString(),
                        holdRow["AssetId"].ToString(),
                        DateUtil.ValidDate(holdRow["HoldDate"].ToString())
                        ));
                }
            } catch (Exception e) {
                throw e;
            } finally {
                dbConn.Close();
            }

            return allHolds;
        }

        /// <summary>
        /// Delete data for a single hold from the database.
        /// </summary>
        /// <param name="hold">The hold to be deleted.</param>
        public void DeleteHold(Hold hold) {
            //Ensure hold already exists
            try {
                OleDbCommand oleDbCmd = new OleDbCommand("DELETE * FROM Hold WHERE [StudentId] = ? AND [AssetId] = ?", dbConn);
                oleDbCmd.Parameters.AddWithValue("StudentId", hold.StudentId);
                oleDbCmd.Parameters.AddWithValue("AssetId", hold.AssetId);
                dbConn.Open();
                if (oleDbCmd.ExecuteNonQuery() < 1) {
                    throw new Exception("Hold not deleted. An unknown error occured");
                }
            } catch (Exception e) {
                throw e;
            } finally {
                dbConn.Close();
            }
        }

        //====================================================================
        //  END - CRUD METHODS
        //====================================================================

    }
}
